insert
    overwrite
    table jms_dm.dm_tab_departure_build_package_network_summary_dt
    partition
    (dt)
select t1.scan_site_code             --建包中心code
     , t1.scan_site                  --建包中心name
     , t1.site_agent_code            --建包中心所属代理区code
     , t1.site_agent_name            --建包中心所属代理区name
     , t1.pick_network_code          --取件网点code
     , t2.name  as pick_network_name --取件网点name
     , t1.date_time                  --扫描日期
     , count(1) as buil_bag_num      --建包票数
     , t1.dt
from jms_dm.dm_tab_departure_build_package_waybill_detail_dt t1
         left join jms_dim.dim_network_whole_massage t2
                   on t1.pick_network_code = t2.code
where dt >= date_add('{{ execution_date | cst_ds }}', -0)
  and dt <= '{{ execution_date | cst_ds }}'
  and pick_network_code is not null
group by t1.scan_site_code    --建包中心code
       , t1.scan_site         --建包中心name
       , t1.site_agent_code   --建包中心所属代理区code
       , t1.site_agent_name   --建包中心所属代理区name
       , t1.pick_network_code --取件网点code
       , t2.name              --取件网点name
       , t1.date_time         --扫描日期
       , t1.dt
    distribute by dt
;
